﻿Imports System
Imports System.IO
Imports System.Collections
Imports GBDZafiro
Imports Microsoft.Office.Interop
Imports Microsoft.Office.Interop.Excel.Constants
Imports Microsoft.Office.Interop.Excel.XlLineStyle

Public Class FrmMigracion

    Dim dtProd As DataTable
    Private Shared campos(20) As String
    Private Shared i As Integer = 1
    Dim m_Excel As Excel.Application

    Private Sub FrmMigracion_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        lbPath.Text = ""
        lbPercentaje.BackColor = Drawing.Color.Transparent
    End Sub

    Private Sub btCargarProd_Click(sender As System.Object, e As System.EventArgs) Handles btCargarProd.Click
        btCargarProd.Enabled = False
        btValidar.Enabled = False
        btGuardar.Enabled = False
        lbNoDatos.Visible = False
        If Not Producto.existProducto() Then
            cargargvProd()
            If Not gvProd.Rows.Count < 1 Then
                btValidar.Enabled = True
            Else
                lbNoDatos.Visible = True
            End If
        Else
            msnBox.msnInformacion("La Tabla Productos, ya contiene lista de Productos ")
        End If
        btCargarProd.Enabled = True
    End Sub

    Private Sub btValidar_Click(sender As System.Object, e As System.EventArgs) Handles btValidar.Click
        validar()
    End Sub

    Private Sub btGuardar_Click(sender As System.Object, e As System.EventArgs) Handles btGuardar.Click
        Dim Prod As GBDZafiro.Producto
        For Each rProd As DataGridViewRow In gvProd.Rows
            Prod = crearProd(rProd)
            GBDZafiro.Producto.Nuevo(Prod)
        Next
        iniControles()
    End Sub

    Private Function crearProd(ByRef rProd As DataGridViewRow) As GBDZafiro.Producto
        Dim Prod As New GBDZafiro.Producto
        Prod.codProducto = rProd.Cells("CODIGO").Value
        Prod.referencia = rProd.Cells("REFERENCIA").Value
        Prod.descripcion = rProd.Cells("DESCRIPCION").Value
        Prod.existIni = CDec(rProd.Cells("EXIST_INI").Value)
        Prod.idUniMed = rProd.Cells("CODUNIDAD").Value
        Prod.codColor = rProd.Cells("CODCOLOR").Value
        Prod.codTalla = rProd.Cells("CODTALLA").Value
        Prod.idSubgrupo = rProd.Cells("CODSUBGRUPO").Value
        Prod.stockMax = rProd.Cells("STOCK_MAX").Value
        Prod.stockMin = rProd.Cells("STOCK_MIN").Value
        Prod.costoUni = rProd.Cells("COSTO_UNIT").Value
        Prod.precio1 = rProd.Cells("PRECIO_1").Value
        Prod.precio2 = rProd.Cells("PRECIO_2").Value
        Prod.precio3 = rProd.Cells("PRECIO_3").Value
        Prod.porcDcto = rProd.Cells("DCTO").Value
        Prod.porcIVA = rProd.Cells("IVA").Value
        Prod.bdReferencia = rProd.Cells("BODEGA_REF").Value
        Prod.estante = ""
        Prod.nomArchivo = ""
        Prod.codUsuario = Login.usuario.codUsuario
        Return Prod
    End Function

    Private Sub cargargvProd()
        gvProd.DataSource = Nothing
        gvProd.Rows.Clear()

        dtProd = New DataTable
        Dim j As Integer
        Dim drProd As DataRow
        Dim objReader As StreamReader
        Dim TestArray() As String
        Dim sLine As String = ""
        Dim extension As String
        opfProd.Filter = "Ficheros TXT|*.txt|Excel Files|*.xls;*.xlsx"
        opfProd.ShowDialog()
        If opfProd.FileName.Length < 1 Then
            Exit Sub
        End If

        extension = Path.GetExtension(opfProd.FileName)
        extension = extension.ToLower
        lbPath.Text = opfProd.FileName

        If extension = ".txt" Then

            objReader = New StreamReader(opfProd.FileName)

            Dim max As Integer = 0
            Do
                sLine = objReader.ReadLine()
                max += 1
            Loop Until sLine Is Nothing
            objReader.Close()

            With pbarMigracion
                .Maximum = max - 1
                .Minimum = 0
                .Value = 0
            End With


            objReader = New StreamReader(opfProd.FileName)
            sLine = objReader.ReadLine()
            TestArray = Split(sLine, ";")
            j = TestArray.Length

            For Each item As String In TestArray
                item = Trim(item).ToUpper
                With dtProd
                    If item = "PRECIO_1" Or item = "PRECIO_2" Or item = "PRECIO_3" Or item = "STOCK_MAX" Or _
                        item = "STOCK_MIN" Or item = "COSTO_UNIT" Or item = "DCTO" Then
                        .Columns.Add(item, Type.GetType("System.Decimal"))

                    Else
                        .Columns.Add(item, Type.GetType("System.String"))

                    End If
                End With
            Next

            max = 2
            Do
                sLine = objReader.ReadLine()
                If Not sLine Is Nothing Then

                    TestArray = Split(sLine, ";")
                    drProd = dtProd.NewRow()

                    For i = 0 To j - 1
                        Try
                            TestArray(i) = TestArray(i).Trim()
                            TestArray(i) = validarNum(TestArray(i))
                            drProd.Item(i) = TestArray(i)
                        Catch ex As ArgumentException
                            drProd.Item(i) = 0D
                        End Try
                    Next

                    dtProd.Rows.Add(drProd)

                    pbarMigracion.Value = max
                    mostrarPorcentage(max, pbarMigracion.Maximum)
                    max += 1
                End If
            Loop Until sLine Is Nothing
            objReader.Close()

        Else
            dtProd = openExcel(opfProd.FileName)
        End If

        gvProd.DataSource = dtProd
        formatDinero()
    End Sub

    Private Function openExcel(filename As String) As DataTable
        Dim dtProd As New DataTable
        Dim objLibroExcel As Excel.Workbook
        Dim objHojaExcel As Excel.Worksheet
        Dim NumRows As Integer
        m_Excel = New Excel.Application

        'm_Excel.Workbooks.Open(Filename:=filename)
        objLibroExcel = m_Excel.Workbooks.Open(Filename:=filename) 'm_Excel.Workbooks.Add()
        objHojaExcel = objLibroExcel.Worksheets(1)

        If objHojaExcel.Range("A1").Value Is Nothing Then
            objLibroExcel.Close()
            Return dtProd
            Exit Function
        End If

        dtProd.Columns.Add(objHojaExcel.Range("A1").Value.ToString.ToUpper, Type.GetType("System.String"))
        dtProd.Columns.Add(objHojaExcel.Range("B1").Value.ToString.ToUpper, Type.GetType("System.String"))
        dtProd.Columns.Add(objHojaExcel.Range("C1").Value.ToString.ToUpper, Type.GetType("System.String"))
        dtProd.Columns.Add(objHojaExcel.Range("D1").Value.ToString.ToUpper, Type.GetType("System.String"))
        dtProd.Columns.Add(objHojaExcel.Range("E1").Value.ToString.ToUpper, Type.GetType("System.String"))
        dtProd.Columns.Add(objHojaExcel.Range("F1").Value.ToString.ToUpper, Type.GetType("System.String"))
        dtProd.Columns.Add(objHojaExcel.Range("G1").Value.ToString.ToUpper, Type.GetType("System.String"))
        dtProd.Columns.Add(objHojaExcel.Range("H1").Value.ToString.ToUpper, Type.GetType("System.String"))
        dtProd.Columns.Add(objHojaExcel.Range("I1").Value.ToString.ToUpper, Type.GetType("System.String"))
        dtProd.Columns.Add(objHojaExcel.Range("J1").Value.ToString.ToUpper, Type.GetType("System.Decimal"))
        dtProd.Columns.Add(objHojaExcel.Range("L1").Value.ToString.ToUpper, Type.GetType("System.Decimal"))
        dtProd.Columns.Add(objHojaExcel.Range("K1").Value.ToString.ToUpper, Type.GetType("System.Decimal"))
        dtProd.Columns.Add(objHojaExcel.Range("M1").Value.ToString.ToUpper, Type.GetType("System.Decimal"))
        dtProd.Columns.Add(objHojaExcel.Range("N1").Value.ToString.ToUpper, Type.GetType("System.Decimal"))
        dtProd.Columns.Add(objHojaExcel.Range("O1").Value.ToString.ToUpper, Type.GetType("System.Decimal"))
        dtProd.Columns.Add(objHojaExcel.Range("P1").Value.ToString.ToUpper, Type.GetType("System.Decimal"))
        dtProd.Columns.Add(objHojaExcel.Range("Q1").Value.ToString.ToUpper, Type.GetType("System.String"))
        dtProd.Columns.Add(objHojaExcel.Range("R1").Value.ToString.ToUpper, Type.GetType("System.String"))

        If objHojaExcel.Range("A2").Value Is Nothing Then
            objLibroExcel.Close()
            Return dtProd
            Exit Function
        End If

        NumRows = objHojaExcel.Range("A2", objHojaExcel.Range("A1").End(Excel.XlDirection.xlDown)).Rows.Count
        With pbarMigracion
            .Minimum = 0
            .Maximum = NumRows
            .Value = 0
        End With
        Dim rProd As DataRow
        For i As Integer = 1 To NumRows
            rProd = dtProd.NewRow
            rProd.Item(0) = objHojaExcel.Range("A" + (i + 1).ToString).Value
            rProd.Item(1) = objHojaExcel.Range("B" + (i + 1).ToString).Value
            rProd.Item(2) = objHojaExcel.Range("C" + (i + 1).ToString).Value
            rProd.Item(3) = objHojaExcel.Range("D" + (i + 1).ToString).Value
            rProd.Item(4) = objHojaExcel.Range("E" + (i + 1).ToString).Value
            rProd.Item(5) = objHojaExcel.Range("F" + (i + 1).ToString).Value
            rProd.Item(6) = objHojaExcel.Range("G" + (i + 1).ToString).Value
            rProd.Item(7) = objHojaExcel.Range("H" + (i + 1).ToString).Value
            rProd.Item(8) = objHojaExcel.Range("I" + (i + 1).ToString).Value

            valorNum(objHojaExcel.Range("J" + (i + 1).ToString).Value, rProd, 9)
            valorNum(objHojaExcel.Range("K" + (i + 1).ToString).Value, rProd, 10)
            valorNum(objHojaExcel.Range("L" + (i + 1).ToString).Value, rProd, 11)
            valorNum(objHojaExcel.Range("M" + (i + 1).ToString).Value, rProd, 12)
            valorNum(objHojaExcel.Range("N" + (i + 1).ToString).Value, rProd, 13)
            valorNum(objHojaExcel.Range("O" + (i + 1).ToString).Value, rProd, 14)
            valorNum(objHojaExcel.Range("P" + (i + 1).ToString).Value, rProd, 15)

            rProd.Item(16) = objHojaExcel.Range("Q" + (i + 1).ToString).Value
            rProd.Item(17) = objHojaExcel.Range("R" + (i + 1).ToString).Value
            dtProd.Rows.Add(rProd)
            mostrarPorcentage(i, pbarMigracion.Maximum)
            pbarMigracion.Value = i
        Next

        objLibroExcel.Close()
        Return dtProd
    End Function

    Private Sub valorNum(ByVal val As String, ByRef row As DataRow, ByRef i As Integer)
        If val Is Nothing Then
            row.Item(i) = "0"

        Else
            row.Item(i) = validarNum(val)

        End If
    End Sub

    Private Function validarNum(text As String) As String
        If IsNumeric(text) And Not InStr(1, text, ".", CompareMethod.Text) = 0 Then
            text = text.Substring(0, InStr(1, text, ".", CompareMethod.Text) - 1)
            Return text
        Else
            Return text
        End If
    End Function

    Private Sub validar()
        i = 1
        campos = Nothing
        ReDim campos(20)

        Dim prod As String
        Dim columnas As String = ""

        For Each rProd As DataRow In dtProd.Rows
            For Each cProd As DataColumn In dtProd.Columns
                cProd.ColumnName = cProd.ColumnName.Trim
                If cProd.ColumnName = "REFERENCIA" Or _
                    cProd.ColumnName = "EXIST_INI" Or _
                     cProd.ColumnName = "DCTO" Or _
                     cProd.ColumnName = "BODEGA_REF" Then

                Else
                    prod = rProd(cProd).ToString
                    prod = Trim(prod)
                    If prod.Length < 1 Or prod Is Nothing Then
                        If Not valCadena(cProd.ColumnName) Then
                            columnas += cProd.ColumnName + ", "
                        End If
                    End If
                End If
            Next
        Next

        If Not columnas.Length < 1 Then
            columnas = Mid(columnas, 1, Len(columnas) - 1)
            msnBox.msnExclamacion("Algunos productos tiene columnas vacias o no existen en la base de datos: " + columnas)
            btValidar.Enabled = False
            Exit Sub
        End If

        validarExist()
    End Sub

    Private Sub validarExist()
        Dim UndMed As New GBDZafiro.UnidadMedida
        Dim color As New GBDZafiro.Color
        Dim talla As New GBDZafiro.Talla
        Dim grp As New GBDZafiro.Grupo
        Dim subGrp As New GBDZafiro.SubGrupo
        Dim dtSubGrp As DataTable

        For Each rProd As DataGridViewRow In gvProd.Rows
            UndMed.idUndMed = rProd.Cells("CODUNIDAD").Value
            If Not GBDZafiro.UnidadMedida.exisUndMed(UndMed) Then
                msnBox.msnExclamacion("Uno o Varias Unidades de medida no existen en la base de datos!!!")
                Exit Sub
            End If

            color.CodColor = rProd.Cells("CODCOLOR").Value
            If Not GBDZafiro.Color.exisColor(color) Then
                msnBox.msnExclamacion("Uno o Varios Colores no existen en la base de datos!!!")
                Exit Sub
            End If

            talla.codTalla = rProd.Cells("CODTALLA").Value
            If Not GBDZafiro.Talla.exisTalla(talla) Then
                msnBox.msnExclamacion("Uno o Varias Tallas no existen en la base de datos!!!")
                Exit Sub
            End If

            grp.idGrupo = rProd.Cells("CODGRUPO").Value
            If Not GBDZafiro.Grupo.exisGrupo(grp) Then
                msnBox.msnExclamacion("Uno o Varios Grupos no existen en la base de datos!!!")
                Exit Sub
            End If

            subGrp.idSubGrupo = rProd.Cells("CODSUBGRUPO").Value
            If Not GBDZafiro.SubGrupo.exisSubGrupo(subGrp) Then
                msnBox.msnExclamacion("Uno o Varios SubGrupos no existen en la base de datos!!!")
                Exit Sub
            End If

            dtSubGrp = GBDZafiro.SubGrupo.ConsxIdSubGrupo(subGrp)
            For Each rSubGrp As DataRow In dtSubGrp.Rows
                If Not rSubGrp("ID_GRUPO") = grp.idGrupo Then
                    msnBox.msnExclamacion("Uno o varios grupos no pertenecen a su respectivo subgrupo!!!")
                    Exit Sub
                End If
            Next

        Next

        btGuardar.Enabled = True
    End Sub

    Private Function valCadena(cadena As String) As Boolean
        If campos(1) Is Nothing Then
            campos(1) = cadena
            i += 1
            Return False
        Else
            For index = 1 To campos.Length - 1
                If campos(index) = cadena Then
                    Return True
                    Exit Function
                End If
            Next
            campos(i) = cadena
            i += 1
            Return False
        End If
    End Function

    Private Sub mostrarPorcentage(ByRef min As Integer, ByRef max As Integer)
        lbPercentaje.Text = ((min * 100) / max).ToString + "%"
    End Sub

    Private Sub formatDinero()
        With gvProd
            .Columns("PRECIO_1").DefaultCellStyle.Format = "N"
            .Columns("PRECIO_2").DefaultCellStyle.Format = "N"
            .Columns("PRECIO_3").DefaultCellStyle.Format = "N"
            .Columns("STOCK_MAX").DefaultCellStyle.Format = "N"
            .Columns("STOCK_MIN").DefaultCellStyle.Format = "N"
            .Columns("COSTO_UNIT").DefaultCellStyle.Format = "N"
            .Columns("DCTO").DefaultCellStyle.Format = "N"
            .Columns("PRECIO_1").DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
            .Columns("PRECIO_2").DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
            .Columns("PRECIO_3").DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
            .Columns("STOCK_MAX").DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
            .Columns("STOCK_MIN").DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
            .Columns("COSTO_UNIT").DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
            .Columns("DCTO").DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
        End With
    End Sub

    Private Sub iniControles()
        gvProd.DataSource = Nothing
        btGuardar.Enabled = False
        btValidar.Enabled = False
        pbarMigracion.Value = 0
        lbPercentaje.Text = "0%"
    End Sub

End Class